package com.example.skycodes.dao;

import com.example.skycodes.bean.NoticeBean;
import com.example.skycodes.util.DBConnection;
import com.example.skycodes.util.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NoticeDao {
    DBConnection DBConn = new DBConnection();
    public List<NoticeBean> getList() {
        Connection conn = DBConn.getConn();
        String sql = "select * from Notice limit \"+(page-1)*10+\",10";
        List<NoticeBean> list = new ArrayList<NoticeBean>();
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                NoticeBean NoticeBean = new NoticeBean();
                NoticeBean.setNoticeID(rs.getInt("NoticeID"));
                NoticeBean.setNoticeTitle(rs.getString("NoticeTitle"));
                NoticeBean.setNoticeContent(rs.getString("NoticeContent"));
                NoticeBean.setNoticeTime(Time.getDate());
                NoticeBean.setAdminName(rs.getString("AdminName"));
                list.add(NoticeBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int add(NoticeBean Notice) {
        Connection conn = DBConn.getConn();
        String sql = "insert into Notice values(null,?,?,?,?)";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, Notice.getNoticeTitle());
            pmst.setString(2, Notice.getNoticeContent());
            pmst.setString(3, Time.getDate());
            pmst.setString(4, Notice.getAdminName());
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;//添加失败返回0
    }

    public int update(NoticeBean Notice) {
        Connection conn = DBConn.getConn();
        String sql = "update Notice set NoticeTitle=?,NoticeContent=?,NoticeTime=?,AdminName=? where NoticeID=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, Notice.getNoticeTitle());
            pmst.setString(2, Notice.getNoticeContent());
            pmst.setString(3, Time.getDate());
            pmst.setString(4, Notice.getAdminName());
            pmst.setInt(5, Notice.getNoticeID());
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;//添加失败返回0
    }

    public int delete(int NoticeID) {
        Connection conn = DBConn.getConn();
        try {
            String sql = "delete from Notice where NoticeID=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, NoticeID);
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public int count(){
        Connection conn = DBConn.getConn();
        try{
            String sql = "select count(*) from news";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);//cout查询返回第一行数据 第一行第一列就是总条数
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
}

